Amazon Redshift: マテリアライズド・ビュー(Materialized View)が一般利用可能になりました

2019年11月27日時点でプレビュー版の利用開始がアナウンスされていたAmazon Redshiftの『マテリアライズド・ビュー』。

公式ドキュメントではクラスターバージョンが『1.0.13059から一般利用開始』とアナウンスされていましたが、

• Document History - Amazon Redshift

手元のRedshiftクラスタのバージョンを確認してみたところ、条件を満たしていました。

#  SELECT VERSION();
                    version                                                          
---------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, 
 compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), 
 Redshift 1.0.13448
(1 row)

なので、早速一般利用可能となったAmazon Redshfifの『マテリアライズド・ビュー(MATERIALIZED VIEW)』について試してみたいと思います。

• Creating Materialized Views in Amazon Redshift - Amazon Redshift

マテリアライズド・ビューの作成(CREATE MATERIALIZED VIEW)

• CREATE MATERIALIZED VIEW - Amazon Redshift

構文

マテリアライズド・ビューの作成(CREATE MATERIALIZED VIEW)のシンタックスは以下。

mv_name

マテリアライズド・ビューの名前。スキーマ名併記可能。スキーマ名省略の場合はpublicスキーマに作成されます。

BACK_UP

マテリアライズド・ビューをクラスタのスナップショット(自動・手動合わせて)の中に含めるかどうかを指定。デフォルトはBACKUP YES。

table_attributes

データの配布方法をオプションで指定可能。

• 分散スタイル指定(DISTSTYLE { EVEN | ALL | KEY })
• 分散キー指定(DISTKEY ( distkey_identifier ))
• ソートキー指定(SORTKEY ( column_name [, ...] ))

AS query

定義する結果を予めクエリ、計算しておくために必要となる、文法的に有効なSELECT文。制限については個別に定められたものがあり、これらに従う必要があります。(※参照)

ベースとなるテーブルには、参照している「マテリアライズド・ビュー」に影響を与えること無く、列の追加は可能です。また、以下のものについては「情報の変更」は不可能。必要に応じてマテリアライズド・ビューの削除→再作成を行う形となります。

• 「列の名前変更、削除」「列のデータ型の変更」「スキーマ名の変更」等
• マテリアライズド・ビューの定義(SQLステートメント)
• マテリアライズド・ビューの名前

必要な権限

CREATE MATERIALIZED VIEWコマンドを利用するには以下の権限が必要となります。

• スキーマに於けるCREATE権限
• 作成するために用いるベーステーブルに於ける、テーブル単位でのSELECT権限(特定の列に対して列レベルの権限を持っている場合でも、それらの列のみにマテリアライズドビューを作成することは出来ない)

制限事項

以下の要素を含むマテリアライズド・ビューは作成出来ません。

• その他のマテリアライズド・ビュー(入れ子は出来ない、ということ)
• 標準のビュー(ただし、マテリアライズド・ビューを参照する標準ビューを作成する事は可能)
• Amazon Redshift Spectrumまたはフェデレーションクエリで使用されるような外部テーブル
• システムテーブル、システムビュー
• 一時テーブル
• ユーザー定義関数(UDF: User-defined functions)
• ORDER BY句
• LIMIT句
• OFFSET句

マテリアライズドビューは結果を計算し、作成時に保存しています。これは即ち、「マテリアライズドビューの作成時に遅延バインディングを実行できない」ということを意味します。このため、

• マテリアライズドビューの定義SQLクエリで参照されるベーステーブル、または関連する列は予め存在しており、且つ有効であること
• CREATE MATERIALIZED VIEWステートメントを発行する前に、SELECT特権が付与されていること

を確認しておく必要があります。

また、マテリアライズド・ビューの削除については下記をご参照ください。

• DROP MATERIALIZED VIEW - Amazon Redshift

マテリアライズド・ビュー作成：実践

ブログ投稿データと著者マスタを結合させ「2020年の投稿」に限定させたものをマテリアライズド・ビューとして作成してみました。

# DROP MATERIALIZED VIEW IF EXISTS cmdevio.blog_posts_2020;
INFO:  Materialized View "blog_posts_2020" does not exist and will be skipped
DROP MATERIALIZED VIEW

# CREATE MATERIALIZED VIEW cmdevio.blog_posts_2020
BACKUP YES
DISTSTYLE EVEN
SORTKEY(post_date)
AS
SELECT
  cmdevio.t_blogposts.post_id,
  cmdevio.t_blogposts.post_date,
  TRIM(cmdevio.t_blogposts.title),
  cmdevio.t_blogposts.url,
  cmdevio.t_blogposts.author_id,
  cmdevio.t_blogposts.social,
  cmdevio.t_blogposts.twitter,
  cmdevio.t_blogposts.facebook,
  cmdevio.t_blogposts.hatena,
  cmdevio.t_blogposts.view,
  cmdevio.m_authors.nick_name,
  cmdevio.m_authors.business_headquarters,
  cmdevio.m_authors.department

FROM
  cmdevio.t_blogposts,
  cmdevio.m_authors

WHERE
      cmdevio.t_blogposts.author_id = cmdevio.m_authors.author_id
  AND cmdevio.m_authors.is_valid = True
  AND cmdevio.t_blogposts.post_date >= '2020-01-01';

CREATE MATERIALIZED VIEW

ちなみにテーブル結合の部分について、INNER JOINで以下の形で置き換えて実行する分には特に問題ありませんでしたが、

  :
FROM
  cmdevio.t_blogposts
    INNER JOIN cmdevio.m_authors ON cmdevio.t_blogposts.author_id = cmdevio.m_authors.author_id
  :
  AND cmdevio.t_blogposts.post_date >= '2020-01-01';
CREATE MATERIALIZED VIEW

LEFT OUTER JOINで試してみたら警告扱いとなりました。この挙動に関しては用いられているクエリの内容に応じてAmazon Redshift側が自動で判別を行っており、「制限事項」に合致するものが無ければ増分マテリアライズド・ビューとして、合致するものがあった場合は全件更新が行われる(必要がある)ものとして作成される模様。この辺りについては別途エントリを改めて見ていきたいと思います。

• REFRESH MATERIALIZED VIEW - Amazon Redshift

  :
FROM
  cmdevio.t_blogposts
    LEFT OUTER JOIN cmdevio.m_authors ON cmdevio.t_blogposts.author_id = cmdevio.m_authors.author_id
  :
  AND cmdevio.t_blogposts.post_date >= '2020-01-01';
WARNING:  An incrementally maintained materialized view could not be created, reason: Subqueries are not supported. 
The materialized view created, cmdevio.blog_posts_2020, will be recomputed from scratch for every REFRESH.
CREATE MATERIALIZED VIEW

また、以下のような形で結果が都度変わる可能性のある関数(mutable functions)も駄目でした。

/** システム日付から過去3日分の投稿をまとめるマテビューが作りたかった. */
# CREATE MATERIALIZED VIEW cmdevio.past_3days_post
AS
SELECT post_date, post_id, author_id, title
FROM cmdevio.t_blogposts
WHERE post_date >= DATEADD(day, -3, sysdate);
ERROR:  Materialized views cannot be defined on mutable functions.

/** こんな感じで都度(毎日)作り変える必要がありそう. */
# DROP MATERIALIZED VIEW IF EXISTS cmdevio.past_3days_post;
DROP MATERIALIZED VIEW

# CREATE MATERIALIZED VIEW cmdevio.past_3days_post
# AS
# SELECT post_date, post_id, author_id, title
# FROM cmdevio.t_blogposts WHERE post_date >= '2020-03-06';
CREATE MATERIALIZED VIEW

その他、上記「制限事項」に挙げられていた要素を含んだコマンド実行結果を幾つか挙げておきます。

ORDER BY句を含めてCREATE MATERIALIZED VIEWを実行：

  :
WHERE
      cmdevio.m_authors.is_valid = True
  AND cmdevio.t_blogposts.post_date >= '2020-01-01'
ORDER BY post_date ASC;
ERROR:  Materialized views do not support ORDER BY clauses.

LIMIT句を含めてCREATE MATERIALIZED VIEWを実行：

WHERE
      cmdevio.m_authors.is_valid = True
  AND cmdevio.t_blogposts.post_date >= '2020-01-01'
LIMIT 100;
ERROR:  Materialized views do not support LIMIT clauses.

作成したマテリアライズド・ビューを用いてのマテリアライズド・ビューを作成：

# CREATE MATERIALIZED VIEW cmdevio.blog_posts_19_and20
BACKUP YES
DISTSTYLE EVEN
SORTKEY(post_date)
AS
SELECT * FROM cmdevio.blog_posts_2020
UNION ALL
SELECT
  :
  :
FROM
  cmdevio.t_blogposts
    INNER JOIN cmdevio.m_authors ON cmdevio.t_blogposts.author_id = cmdevio.m_authors.author_id
WHERE
      cmdevio.m_authors.is_valid = True
  AND cmdevio.t_blogposts.post_date BETWEEN '2019-01-01' AND '2019-12-31';
ERROR:  Materialized views cannot be defined on regular or late binding views.

まとめ

という訳で、一般利用可能となったAmazon Redshiftのマテリアライズド・ビュー(Materialized View)の実践例のご紹介でした。

ビュー作成には幾つかの制約が存在していますので、実利用の際にはこの辺りのポイントを踏まえて上手く活用していきたいところですね。